package shopping.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import shopping.domain.Product;
import shopping.constants.Global;
import shopping.utils.DataSourceUtil;

import java.sql.SQLException;
import java.util.List;

public class ProductDao {

    public List<Product> findHots() {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        /*
            热门商品，数据库中is_hot字段标识热门商品（1为热门，0为不是热门），pflag = 1标识是上架商品，按时间排序，一页最多显示9个。

            这里需要注意一个问题，pflag =1 和 is_hot = 1都是写死的，
            那么当数据库中的pflag被重新定义时，所有需要使用pflag字段的sql语句都需要修改。

            这里可以使用"面向对象思想"来解决，可以使用一个类/接口，
            来定义此处的pflag和is_got，这样当字段被改变时，只需要改变类/接口中对应的值即可。

            使用Global接口来实现
         */
//      String sql = "select * from product where pflag=1 and is_hot=1 order by pdate desc limit 9";
        String sql = "select * from product where pflag=? and is_hot=? order by pdate desc limit 0,9";
        try {
            return qr.query(sql, new BeanListHandler<>(Product.class), Global.PRODUCT_PFLAG_ON, Global.PRODUCT_IS_HOT);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public List<Product> findNews() {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        /**
         * 所谓最新，就是按时间排序。
         */
        String sql = "select * from product where pflag=? order by pdate desc limit 0,9";

        try {
            return qr.query(sql, new BeanListHandler<>(Product.class), Global.PRODUCT_PFLAG_ON);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public Product findById(String pid) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product where pid = ?";

        try {
            return qr.query(sql, new BeanHandler<>(Product.class), pid);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public List<Product> findByPageWithCid(String cid, int pageNumber, int pageSize) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product where cid = ? limit ?,?";

        try {
            return qr.query(sql, new BeanListHandler<>(Product.class), cid, (pageNumber - 1) * pageSize, pageSize);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public int count(String cid) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select count(*) from product where cid = ?";

        try {
            return ((Long)qr.query(sql, new ScalarHandler(), cid)).intValue();
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public List<Product> findByPage(int pageNumber, int pageSize) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product limit ?,?";

        try {
            return qr.query(sql, new BeanListHandler<>(Product.class),  (pageNumber - 1) * pageSize, pageSize);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public int count() {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select count(*) from product";

        try {
            return ((Long)qr.query(sql, new ScalarHandler())).intValue();
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    public void save(Product p) {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "insert into product values(?,?,?,?,?,?,?,?,?,?)";

        try {
            qr.update(sql,
                    p.getPid(),p.getPname(), p.getMarket_price(),
                    p.getShop_price(), p.getPimage(), p.getPdate(),
                    p.getIs_hot(),p.getPdesc(), p.getPflag(), p.getCid()
            );
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }
}
